﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// Summary description for Xuly
/// </summary>
public class Xuly
{
	public SqlConnection sqlCn;
    private string strErrorMessage;
    private int intErrorNumber;
    
    private string sConnectionString;
	public Xuly()
	{
		
	}
    
    public string ConnectionString
    {
        get
        {
            return sConnectionString;
        }
        set
        {
            sConnectionString = value;
        }
    }
    // Ham mo ket noi
    public void OpenConnection(String str)
    {
        sConnectionString = WebConfigurationManager.ConnectionStrings[str].ToString();

    }
    #region Connect Data
    public SqlConnection openConnect()
    {
        SqlConnection sqlCn = new SqlConnection(ConnectionString);
        try
        {
            sqlCn.Open();
        }
        catch
        {
            return null;
        }
        return sqlCn;
    }

    public void Disconnect(SqlConnection sqlCn)
    {
        if (sqlCn != null)
        {
            if (sqlCn.State == ConnectionState.Open)
                sqlCn.Close();
            sqlCn.Dispose();
        }
    }

    #endregion

    /// <summary>
    /// String: Câu thông báo lỗi khi truy cập SQL
    /// </summary>
    /// 
    public string ErrorMessage
    {
        get
        {
            return strErrorMessage;
        }
    }
    /// <summary>
    /// int: Số lỗi khi truy cập SQL
    /// </summary>
    public int ErrorNumber
    {
        get
        {
            return intErrorNumber;
        }
    }

    /// <summary>
    /// Thực thi 1 StoreProcedure có trả về dữ liệu là 1 bảng
    /// </summary>
    /// <return>
    /// DataTable
    /// </return>>
    protected DataTable ExcuteStoreTable(string TenStoreProcedure, SqlParameter[] sqlParam)
    {
        DataTable dtbTmp = new DataTable();
        OpenConnection("msmart2012ConnectionString");
        try
        {
            sqlCn = openConnect();
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandTimeout = 2000;
            sqlCmd.Connection = sqlCn;
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = TenStoreProcedure;
            for (int i = 0; i < sqlParam.Length; i++)
            {
                sqlCmd.Parameters.Add(sqlParam[i]);
            }
            SqlDataAdapter sqlDA = new SqlDataAdapter();
            sqlDA.SelectCommand = sqlCmd;
            sqlDA.Fill(dtbTmp);
            Disconnect(sqlCn);
        }
        catch (SqlException sqlEx)
        {
            strErrorMessage = sqlEx.Message;
            intErrorNumber = sqlEx.Number;
        }
        finally
        {
            if (sqlCn.State == ConnectionState.Open)
                sqlCn.Close();
            sqlCn.Dispose();

        }
        return dtbTmp;
    }

    public DataTable GetInformationByID(int intID,String columname,String Proc)
    {
        SqlParameter[] arrParam = 
            {
                new SqlParameter(columname, SqlDbType.Int) 
            };
        arrParam[0].Value = intID;
        return ExcuteStoreTable(Proc, arrParam);
    }
    /// <summary>
    /// Thực thi 1 câu lệnh SQL dạng select có trả về dữ liệu là 1 bảng
    /// </summary>
    /// <return>
    /// DataTable
    /// </return>>
    public DataTable ExcuteSQL_Table(string strSQL)
    {
        OpenConnection("msmart2012ConnectionString");
        sqlCn = openConnect();
        SqlDataAdapter Adapter = new SqlDataAdapter(strSQL, sqlCn);
        DataTable ds = new DataTable();
        try
        {
            Adapter.Fill(ds);
            Disconnect(sqlCn);
        }
        catch (SqlException E)
        {
            string strDescriptionError = E.Message;
        }
        finally
        {
            if (sqlCn.State == ConnectionState.Open)
                sqlCn.Close();
            sqlCn.Dispose();
        }
        return ds;

    }
    public DataTable SelectCategory(string CateName)
    {
        return ExcuteSQL_Table(CateName);
    }
    public DataTable GetInfo(String Clum,String id,String proc)
    {
        SqlParameter[] arrParam = 
            {
                new SqlParameter(Clum, SqlDbType.NVarChar) 
            };
        arrParam[0].Value = id;
        return ExcuteStoreTable(proc, arrParam);
 
    }
}